Visão Geral do Modelo
O sistema é composto basicamente por seis grandes áreas:
-
Alunos
-
Professores
-
Cursos
-
Turmas
-
Disciplinas
-
Matrícula
Entidades (tabelas) e Atributos (colunas)
PESSOA
-
id_pessoa (PK)
-
nome
-
data_nascimento
-
sexo
Obs: Entidade genérica que representa indivíduos do sistema.
ALUNO
-
id_aluno (PK)
-
id_pessoa (FK, UNIQUE)
Obs: Especialização de Pessoa.
PROFESSOR
-
id_professor (PK)
-
formacao
-
id_pessoa (FK, UNIQUE)
Obs: Especialização de Pessoa.
-
id_email (PK)
-
email
-
id_pessoa (FK)
Obs: Uma pessoa pode possuir vários e-mails.
TELEFONE
-
id_telefone (PK)
-
telefone
-
id_pessoa (FK)
Obs: Uma pessoa pode possuir vários telefones.
CURSO
-
id_curso (PK)
-
nome
-
descricao
-
data_inicio
-
data_fim
DISCIPLINA
-
id_disciplina (PK)
-
nome
-
carga_horaria
-
data_inicio
-
data_fim
-
id_curso (FK)
-
id_professor (FK)
TURMA
-
id_turma (PK)
-
nome
-
ano
-
semestre
-
id_curso (FK)
MATRÍCULA
-
id_matricula (PK)
-
data_matricula
-
status
-
id_aluno (FK)
-
id_turma (FK)
Relacionamentos e Cardinalidades
-
PESSOA (1,N) — EMAIL
Uma pessoa pode ter vários e-mails.
-
PESSOA (1,N) — TELEFONE
Uma pessoa pode ter vários telefones.
-
PESSOA (1,1) — ALUNO (especialização)
Um aluno é uma pessoa.
-
PESSOA (1,1) — PROFESSOR (especialização)
Um professor é uma pessoa.
-
CURSO (1,N) — DISCIPLINA
Um curso é composto por uma ou mais disciplinas.
-
PROFESSOR (1,N) — DISCIPLINA
Um professor pode ministrar várias disciplinas.
-
CURSO (1,N) — TURMA
Um curso pode ter várias turmas.
-
ALUNO (1,N) — MATRÍCULA — (1,N) TURMA
Relacionamento muitos-para-muitos entre aluno e turma.
Diagrama Entidade Relacionamento
Script de Criação do Banco de Dados
DROP DATABASE IF EXISTS sistema_escolar;
CREATE DATABASE sistema_escolar;
USE sistema_escolar;
CREATE TABLE pessoa (
id_pessoa INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
data_nascimento DATE,
sexo CHAR(1)
);
CREATE TABLE email (
id_email INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(150) NOT NULL,
id_pessoa INT NOT NULL,
CONSTRAINT fk_email_pessoa
FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);
CREATE TABLE telefone (
id_telefone INT AUTO_INCREMENT PRIMARY KEY,
telefone VARCHAR(20) NOT NULL,
id_pessoa INT NOT NULL,
CONSTRAINT fk_telefone_pessoa
FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);
CREATE TABLE aluno (
id_aluno INT AUTO_INCREMENT PRIMARY KEY,
id_pessoa INT NOT NULL UNIQUE,
CONSTRAINT fk_aluno_pessoa
FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);
CREATE TABLE professor (
id_professor INT AUTO_INCREMENT PRIMARY KEY,
formacao VARCHAR(100),
id_pessoa INT NOT NULL UNIQUE,
CONSTRAINT fk_professor_pessoa
FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);
CREATE TABLE curso (
id_curso INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
descricao VARCHAR(255),
data_inicio DATE NOT NULL,
data_fim DATE NOT NULL
);
CREATE TABLE disciplina (
id_disciplina INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
carga_horaria INT NOT NULL,
data_inicio DATE NOT NULL,
data_fim DATE NOT NULL,
id_curso INT NOT NULL,
id_professor INT NOT NULL,
CONSTRAINT fk_disciplina_curso
FOREIGN KEY (id_curso) REFERENCES curso(id_curso),
CONSTRAINT fk_disciplina_professor
FOREIGN KEY (id_professor) REFERENCES professor(id_professor)
);
CREATE TABLE turma (
id_turma INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(50) NOT NULL,
ano INT NOT NULL,
semestre INT NOT NULL,
id_curso INT NOT NULL,
CONSTRAINT fk_turma_curso
FOREIGN KEY (id_curso) REFERENCES curso(id_curso)
);
CREATE TABLE matricula (
id_matricula INT AUTO_INCREMENT PRIMARY KEY,
data_matricula DATE NOT NULL,
status VARCHAR(20),
id_aluno INT NOT NULL,
id_turma INT NOT NULL,
CONSTRAINT fk_matricula_aluno
FOREIGN KEY (id_aluno) REFERENCES aluno(id_aluno),
CONSTRAINT fk_matricula_turma
FOREIGN KEY (id_turma) REFERENCES turma(id_turma)
);
INSERT INTO pessoa (nome, data_nascimento, sexo) VALUES
('João Pereira', '2002-05-10', 'M'),
('Maria Oliveira', '2001-08-22', 'F'),
('Pedro Santos', '2003-03-15', 'M'),
('Carlos Silva', '1980-04-12', 'M'),
('Ana Souza', '1985-09-30', 'F');
INSERT INTO email (email, id_pessoa) VALUES
('joao@email.com', 1),
('maria@email.com', 2),
('pedro@email.com', 3),
('carlos@escola.com', 4),
('ana@escola.com', 5);
INSERT INTO telefone (telefone, id_pessoa) VALUES
('11988880001', 1),
('11988880002', 2),
('11988880003', 3),
('11999990001', 4),
('11999990002', 5);
INSERT INTO aluno (id_pessoa) VALUES
(1),
(2),
(3);
INSERT INTO professor (formacao, id_pessoa) VALUES
('Matemática', 4),
('Computação', 5);
INSERT INTO curso (nome, descricao, data_inicio, data_fim) VALUES
('Sistemas de Informação', 'Curso de tecnologia', '2024-02-01', '2027-12-15');
INSERT INTO disciplina (nome, carga_horaria, data_inicio, data_fim, id_curso, id_professor) VALUES
('Algoritmos', 80, '2024-02-15', '2024-06-30', 1, 2),
('Banco de Dados', 80, '2024-08-01', '2024-12-15', 1, 2);
INSERT INTO turma (nome, ano, semestre, id_curso) VALUES
('SI-2024-1', 2024, 1, 1);
INSERT INTO matricula (data_matricula, status, id_aluno, id_turma) VALUES
('2024-02-10', 'ATIVA', 1, 1),
('2024-02-10', 'ATIVA', 2, 1);
Exercícios de SQL – Sistema Escolar
NÍVEL 1 – Consultas Básicas (SELECT)
-
Liste todos os alunos, exibindo apenas o nome e o sexo.
-
Liste todos os professores e suas formações.
-
Exiba todos os cursos com suas datas de início e fim.
-
Liste todas as disciplinas cadastradas.
-
Liste todas as turmas do ano de 2024.
NÍVEL 2 – SELECT com WHERE e ORDER BY
-
Liste os alunos do sexo feminino.
-
Liste os professores com formação em Computação.
-
Liste as disciplinas com carga horária maior ou igual a 80 horas.
-
Exiba as turmas do 1º semestre, ordenadas pelo nome.
-
Liste os cursos que terminam após o ano de 2026.
NÍVEL 3 – JOIN (Relacionamentos)
-
Liste o nome do aluno e o nome da turma em que ele está matriculado.
-
Liste o nome do aluno e o nome do curso que ele está cursando.
-
Liste o nome do professor e as disciplinas que ele ministra.
-
Liste as disciplinas junto com o nome do curso ao qual pertencem.
-
Liste o nome da turma e o nome do curso correspondente.
NÍVEL 4 – JOIN com EMAIL e TELEFONE
-
Liste o nome do aluno e seus e-mails.
-
Liste o nome do professor e seus telefones.
-
Liste todas as pessoas com seus respectivos e-mails.
-
Liste os alunos que possuem mais de um telefone.
-
Liste as pessoas que não possuem e-mail cadastrado.
NÍVEL 5 – Funções de Agregação e GROUP BY
-
Quantos alunos estão matriculados no sistema?
-
Quantas disciplinas existem por curso?
-
Quantos alunos existem em cada turma?
-
Exiba o total de disciplinas ministradas por cada professor.
-
Exiba o ano mais recente de início dos cursos.
NÍVEL 6 – Subconsultas
-
Liste os alunos matriculados na turma "SI-2024-1".
-
Liste os professores que não ministram nenhuma disciplina.
-
Liste os cursos que possuem mais de uma disciplina.
-
Liste os alunos que não estão matriculados em nenhuma turma.
-
Liste as disciplinas cujo período está dentro do intervalo do curso.
NÍVEL 7 – Desafios (Integração Completa)
-
Liste o nome do aluno, e-mail e telefone.
-
Liste o nome do professor, sua formação e as disciplinas que ministra.
-
Liste o nome do curso, suas disciplinas e o professor responsável.
-
Liste os alunos, as turmas e os cursos, ordenados pelo nome do aluno.
-
Gere um relatório com:
-
Nome do curso
-
Quantidade de turmas
-
Quantidade de disciplinas